Snowflakeの3種類のTIMESTAMPの違いをまとめてみた #SnowflakeDB
さがらです。
SnowflakeではTIMESTAMPに関して３種類の型があります。その３種の違いを本記事でまとめてみます。
Snowflakeにおける３種類のTIMESTAMP
Snowflakeでは、以下３種類のTIMESTAMPがあります。
- TIMESTAMP_LTZ
- TIMESTAMP_NTZ
- TIMESTAMP_TZ
それぞれの説明は公式Docにも載っていますが、パッと見ただけでは違いがわからないと思います。（私も最初はわかりませんでした。笑）
そこで、次章から公式Docのサンプルコードに沿って、それぞれの違いをまとめていきます。
TIMESTAMP_LTZ
まず１つ目、TIMESTAMP_LTZについてです。
簡単に言うと、timezoneセッションパラメーターの値に応じて、タイムゾーンが動的に変化するTIMESTAMPです。
まず下記のコードを実行して、timezoneパラメータをAmerica/Los_Angelesにして、テーブル作成とレコードのinsertを行います。
create or replace table ts_test(ts timestamp_ltz);
alter session set timezone = 'America/Los_Angeles';
insert into ts_test values('2014-01-01 16:00:00');
insert into ts_test values('2014-01-02 16:00:00 +00:00');
この上で、以下のSELECT文を実行してみます。
select ts, hour(ts) from ts_test;
１行目2014-01-01 16:00:00としてタイムゾーンを指定せずINSERTしたレコードには、America/Los_Angelesのタイムゾーン「-0800 (UTC -8h)」が追記されて登録されました。
２行目2014-01-02 16:00:00 +00:00としてタイムゾーンを指定してINSERTしたレコードは、America/Los_Angelesのタイムゾーン「-0800 (UTC -8h)」に変換された値が登録されました。
続けて、timezoneパラメータをAmerica/New_Yorkにして、先ほどと同じSELECT文を実行してみます。
alter session set timezone = 'America/New_York'; select ts, hour(ts) from ts_test;
すると、１行目も２行目もAmerica/New_Yorkのタイムゾーン「-0500 (UTC -5h)」に変換された値が表示されました。
このように、timezoneセッションパラメーターの値に応じて値が動的に変化するのが、TIMESTAMP_LTZです。
TIMESTAMP_NTZ
２つ目、TIMESTAMP_NTZについてです。
簡単に言うと、レコード登録時のタイムゾーンもtimezoneセッションパラメータも、どちらの影響も受けないTIMESTAMPです。
まず下記のコードを実行して、timezoneパラメータをAmerica/Los_Angelesにして、テーブル作成とレコードのinsertを行います。
create or replace table ts_test(ts timestamp_ntz);
alter session set timezone = 'America/Los_Angeles';
insert into ts_test values('2014-01-01 16:00:00');
insert into ts_test values('2014-01-02 16:00:00 +00:00');
この上で、以下のSELECT文を実行してみます。
select ts, hour(ts) from ts_test;
下図がこのSELECT文の実行結果ですが、１行目はタイムゾーンを含まない値、２行目はタイムゾーンを含む値をINSERTしましたが、どちらもタイムゾーンがなくなった形で表示されているのがわかると思います。timezoneパラメータのAmerica/Los_Angelesの影響も特にありません。
このように、レコード登録時のタイムゾーンもtimezoneセッションパラメータも、どちらの影響も受けないのがTIMESTAMP_NTZです。
TIMESTAMP_TZ
３つ目、TIMESTAMP_TZについてです。
簡単に言うと、値としてはタイムゾーンを必ず持つが、timezoneセッションパラメーターの影響は値の登録時しか受けないTIMESTAMPです。
まず下記のコードを実行して、timezoneパラメータをAmerica/Los_Angelesにして、テーブル作成とレコードのinsertを行います。
create or replace table ts_test(ts timestamp_tz);
alter session set timezone = 'America/Los_Angeles';
insert into ts_test values('2014-01-01 16:00:00');
insert into ts_test values('2014-01-02 16:00:00 +00:00');
この上で、以下のSELECT文を実行してみます。
select ts, hour(ts) from ts_test;
下図がこのSELECT文の実行結果となります。
１行目2014-01-01 16:00:00としてタイムゾーンを指定せずINSERTしたレコードには、America/Los_Angelesのタイムゾーン「-0800 (UTC -8h)」が追記されました。
２行目2014-01-02 16:00:00 +00:00としてタイムゾーンを指定してINSERTしたレコードは、INSERT時の値のタイムゾーンのまま「+0000」で登録されました。
続けて、timezoneパラメータをAmerica/New_Yorkにして、先ほどと同じSELECT文を実行してみます。
alter session set timezone = 'America/New_York'; select ts, hour(ts) from ts_test;
すると、１行目も２行目も値が変化していないことがわかります。timezoneパラメータの値を変えることで、TIMESTAMP_LTZの場合は値のタイムゾーンも変化していていたのが、TIMESTAMP_TZでは値のタイムゾーンが変化しませんでした。
このように、値としてはタイムゾーンを必ず持つが、timezoneセッションパラメーターの影響は値の登録時しか受けないのがTIMESTAMP_TZです。
３種類のTIMESTAMPの使い分け
ここで、３種類のTIMESTAMPの使い分けについてまとめてみます。
- TIMESTAMP_NTZ ※デフォルト値
- タイムゾーンを特に意識せずに、日時データを保持しておきたい場合
- ※参考：Snowflakeには
DATETIMEという型もありますが、これはTIMESTAMP_NTZのエイリアスです
- TIMESTAMP_TZ
- タイムゾーンまで値を保持した上で、クエリを発行するユーザーのタイムゾーンごとに値を変更させたくない場合
- TIMESTAMP_LTZ
- タイムゾーンまで値を保持した上で、クエリを発行するユーザーのタイムゾーンに合わせて値を動的に変更させたい場合
おまけ：「TIMESTAMP」型について
実は、SnowflakeではTIMESTAMPという型もあります。
なのですが、TIMESTAMPはこれまでに説明した３種類のTIMESTAMP_*のエイリアスで、デフォルトではTIMESTAMP_NTZを意味しています。
もし、TIMESTAMPで別のTIMESTAMP_*を参照させたい場合は、TIMESTAMP_TYPE_MAPPINGというセッションパラメーターをを変更すればOKです。
※対象のセッション中TIMESTAMP型を指定したときにTIMESTAMP_TZが使われるように変更するためのクエリを以下に記しておきます。
alter session set TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_TZ'; show parameters like 'TIMESTAMP_TYPE_MAPPING';
最後に
Snowflakeでの３種類のTIMESTAMPの違いをまとめてみました。
データを管理する上で日時は欠かせない要素ですので、この知識を抑えておくといざというときに困惑せずに済むはずです。
